<?php   // $Id: dep_lister.php 275 2008-08-12 20:41:04Z pcoustillas $ ?>
<?php   
/*Copyright (C) 2003-2004 Guy Hendrickx
Licensed under the terms of the GNU  General Public License:http://www.opensource.org/licenses/gpl-license.php
File Authors:Guy Hendrickx
Modification : Pierre Coustillas*/

require_once("include/verif.php");
require_once("include/config/common.php");
require_once("include/language/$lang.php");
require_once("include/utils.php");
require_once("include/headers.php");

?>
<script type="text/javascript" src="javascripts/confdel.js"></script>
<script type="text/javascript" src="javascripts/tri_formulaire.js"></script>
<script language="javascript" src="javascripts/verifier_form.js" type="text/javascript"></script>
<script type="text/javascript">
	function verif_creer_dep()
	{
		if (verifier('depense','black','date','vide#date_','lib','vide','prix','vide#est_nombre'))
		{
			document.depense.submit();
		} else {
			alert("<?php aff_lang('Une des valeurs saisie n\'est pas correct.\nMerci de corriger avant de poursuivre.') ?>")	
		}

	}
</script>

<table class="page" align="center">
	<tr>
		<td class="page" align="center">
			<?php
			require_once("include/head.php");
			?>
		</td>
	</tr>
	<tr>
		<td class="page" align="center">
			<?php

			if ($user_dep == "n") { 
			echo "<h1>$lang_depense_droit</h1>";
			exit;
			}

			if (isset($_GET['message']))
			{
				echo "<h1>" . stripslashes($_GET['message']) . "</h1>";
			}

			$jour = date("d");
			$mois = date("m");
			$annee = date("Y");

			$sql = "SELECT num, lib, fournisseur, prix, date
				FROM ${tblpref}depense";

			// ----------  DEBUT DE GESTION DES FILTRES ---------------------
			if (isset($_GET['annuler_filtre'])) //vidage des filtres si demandé
			{
				$_SESSION['filtre_dep_four'] = "";
				$_SESSION['filtre_dep_date1'] = "";
				$_SESSION['filtre_dep_date2'] = "";
				$_SESSION['filtre_dep_HT'] = "";
			}

			//$filtre=" WHERE num > 0 AND ";
			$filtre="";

			if (isset($_GET['filtrer']))
			{
				// MAJ des variables de session de filtre si il y a eu demande de filtrage
				if (isset($_GET['filtre_dep_four']) && $_GET['filtre_dep_four']<>"default" )
					$_SESSION['filtre_dep_four']=$_GET['filtre_dep_four'];
				if (isset($_GET['filtre_dep_four']) && $_GET['filtre_dep_four']=="default" )
					unset($_SESSION['filtre_dep_four']);
				if (isset($_GET['filtre_dep_date1']))
					$_SESSION['filtre_dep_date1']=$_GET['filtre_dep_date1'];
				if (isset($_GET['filtre_dep_date2']))
					$_SESSION['filtre_dep_date2']=$_GET['filtre_dep_date2'];
				if (isset($_GET['filtre_dep_HT']))
					$_SESSION['filtre_dep_HT']=$_GET['filtre_dep_HT'];
				if (isset($_GET['filtre_bon_TTC']))
					$_SESSION['filtre_bon_TTC']=$_GET['filtre_bon_TTC'];
			}
			
			//if ( ! isset($_GET['annuler_filtre']) && ! isset($_GET['filtrer']) && ! isset($_SESSION['filtre_bon_date']))


			//Création de la requete. Ne se trouve pas dans le test "Si frm poster" car si le user revient le filtre doit tj etre en place
			if (isset($_SESSION['filtre_dep_four']) && $_SESSION['filtre_dep_four'] != "" )
			{
				$filtre .= "fournisseur='"  . $_SESSION['filtre_dep_four'] . "' AND ";
			}
			if (isset($_SESSION['filtre_dep_date1']) && $_SESSION['filtre_dep_date1'] != "")
			{
				$filtre .= "date" . saisie_to_sql($_SESSION['filtre_dep_date1'],"date") . " AND ";
			}
			if (isset($_SESSION['filtre_dep_date2']) && $_SESSION['filtre_dep_date2'] != "")
			{
				$filtre .= "date" . saisie_to_sql($_SESSION['filtre_dep_date2'],"date") . " AND ";
			}

			if (isset($_SESSION['filtre_dep_HT']) &&$_SESSION['filtre_dep_HT'] != "")
			{
				$filtre .= "prix" . saisie_to_sql($_SESSION['filtre_dep_HT'],"num") . " AND ";
			}

			
			if ($filtre != "")
			{
				$filtre = substr($filtre,0,strlen($filtre) -5); //retirer " AND " de la fin du filtre
				$sql .= " WHERE $filtre ";
			}
			//echo $filtre;

			// ----------------  FIN DE GESTION DES FILTRES  -----------------------------

			$tri_ordre=isset($_GET['tri_ordre'])?$_GET['tri_ordre']:"ASC"; //ordre de tri
			$tri_champ=isset($_GET['tri_champ'])?$_GET['tri_champ']:"date"; // champ de tri

			$sql .= " ORDER BY " . $tri_champ . " " . $tri_ordre;

			//echo $sql;

		?>
			<center>
			<table class="boiteaction" width="80%">
				<tr>
					<td width="50%">
						<form action="dep_creer.php" method="post" name="depense">
			
							<table>
							<caption><?php echo $lang_depense_ajouter; ?></caption>
								<tr>
									<td class="texte0" rowspan="2" width="20%">
										<?php echo "$lang_fournisseur" ?>
									</td>
									<td class="texte0" style="text-align:left">
										<select name='fournisseur'>
											<option value="default"><?php echo $lang_choisissez; ?></option>
											<?php
			
												$rqSql = "SELECT * FROM ${tblpref}depense GROUP by fournisseur ORDER BY fournisseur";
												$result = mysql_query( $rqSql ) or die("Erreur selection depense <br/>" . mysql_error());
			
												while ( $row = mysql_fetch_array( $result))
												{
												$four = $row["fournisseur"];
												$four = stripslashes($four);
												$four2 = addslashes($four);
												?>
												<option value="<?php echo "$four2"; ?>"><?php echo "$four"; ?></option>
											<?php
												}
											?>
										</select>
									</td>
								</tr>
								<tr>
									<td class="texte0">
										<?php aff_lang("Ou entrez le nom d'un founisseur à créer") ?>
										<input name="fourn" type="text" size="30" maxlength="30" />
									</td>
								</tr>
								<tr>
									<td class="texte0" id="lib"><?php echo $lang_libelle; ?></td>
									<td colspan="3" class="texte0" >
										<input name="lib" type="text" size="30" maxlength="30" />
									</td>
								</tr>
								<tr>
									<td class="texte0" id="prix"><?php echo $lang_prix_h_tva;  ?></td>
									<td colspan="3" class="texte0" >
										<input name="prix" type="text" onchange='this.value=this.value.replace(",",".")'/>
									</td>
								</tr>
								<tr>
									<td class="texte0" id="date">Date </td>
									<td colspan="3" class="texte0">
										<input type="text" name="date" size="18" value="<?php echo "$jour/$mois/$annee" ?>" />
											<a href="#" onclick="window.open('include/pop.calendrier.php?frm=depense&amp;ch=date','calendrier','width=415,height=160,scrollbars=0').focus();"><img src="image/petit_calendrier.gif" alt="calendrier" border="0"/></a>
									</td>
								</tr>
								<tr>
									<td class="submit" colspan="6">
										<input type="button" onclick="verif_creer_dep()" value="<?php aff_lang("  Ajouter cette dépense  ") ?>" />
									</td>
								</tr>
							</table>

						</form>
					</td>
					<td width="10%">&nbsp;</td>
					<td width="40%">
						<?php //tableau de filtre ?>
						<form action="dep_lister.php" method="get" name="frm_dep">
							<input type="hidden" name="filtrer"/>
							<table width="90%">
								<caption><?php aff_lang("Filtre") ?></caption>
								<tr>
									<td class="texte0"><?php aff_lang("Fournisseur") ?></td>
									<td class="texte0">
										<select name='filtre_dep_four'>
											<option value="default"><?php echo $lang_choisissez; ?></option>
											<?php
			
												$rqSql = "SELECT * FROM ${tblpref}depense GROUP by fournisseur ORDER BY fournisseur";
												$result = mysql_query( $rqSql ) or die("Erreur selection depense <br/>" . mysql_error());
			
												while ( $row = mysql_fetch_array( $result))
												{
												$four = $row["fournisseur"];
												$four = stripslashes($four);
												$four2 = addslashes($four);
												?>
												<option value="<?php echo "$four2" ?>" <?php if ($four==$_SESSION['filtre_dep_four']) echo "selected='selected'" ?> ><?php echo "$four"; ?></option>
											<?php
												}
											?>
										</select>
									</td>
									<td class="submit" rowspan="4" width="20%">
										<br/><br/>
										<input type="submit" value="Filtrer !"/>
										<br/><br/>
										<a href="dep_lister.php?annuler_filtre=oui"><img border="0" src="image/delete.png" alt="<?php echo $lang_supprimer; ?>" /></a>
										<br/><br/>
										<?php bouton_aide("filtre") ?>
									</td>
								</tr>
								<tr>
									<td class="texte0"><?php aff_lang("Date 1") ?></td>
									<td class="texte0">
										<input type="text" size="14" name="filtre_dep_date1" value="<?php echo isset($_SESSION['filtre_dep_date1'])?$_SESSION['filtre_dep_date1']:"" ?>" />
										<a href="#" onclick="window.open('include/pop.calendrier.php?frm=frm_dep&amp;ch=filtre_dep_date1','calendrier','width=415,height=190,left=200,top=200,scrollbars=0').focus();">
											<img src="image/petit_calendrier.gif" alt="calendrier" border="0"/>
										</a>
									</td>
								</tr>
								<tr>
									<td class="texte0"><?php aff_lang("Date 2") ?></td>
									<td class="texte0">
										<input type="text" size="14" name="filtre_dep_date2" value="<?php echo isset($_SESSION['filtre_dep_date2'])?$_SESSION['filtre_dep_date2']:"" ?>" />
										<a href="#" onclick="window.open('include/pop.calendrier.php?frm=frm_dep&amp;ch=filtre_dep_date2','calendrier','width=415,height=190,left=200,top=200,scrollbars=0').focus();">
											<img src="image/petit_calendrier.gif" alt="calendrier" border="0"/>
										</a>
									</td>
								</tr>
								<tr>
									<td class="texte0"><?php aff_lang("Montant HT") ?></td>
									<td class="texte0">
										<input type="text" name="filtre_dep_HT" value="<?php echo isset($_SESSION['filtre_dep_HT'])?$_SESSION['filtre_dep_HT']:"" ?>" />
									</td>
								</tr>
							</table>
						</form>

					</td>
				</tr>
			</table>

			</center>
		</td>
	</tr>

	<tr><td><br/><hr/><br/></td></tr>

	<tr>
		<td>

			<?php

			$req = mysql_query($sql);
			?>

			<form name="tri" method="get" action="#">
				<input type="hidden" name="tri_champ" value="<?php echo $tri_champ ?>" />
				<input type="hidden" name="tri_ordre" value="<?php echo $tri_ordre ?>" />
			</form>

			<center>
			<table>
				<caption><?php echo $lang_depenses_liste; ?></caption>
				<tr>
					<th><a href="javascript:ecrit_ordre_et_post('tri','num')"><?php echo $lang_numero; ?></a></th>
					<th><a href="javascript:ecrit_ordre_et_post('tri','lib')"><?php echo $lang_libelle; ?></a></th>
					<th><a href="javascript:ecrit_ordre_et_post('tri','fournisseur')"><?php echo $lang_montant; ?></a></th>
					<th><a href="javascript:ecrit_ordre_et_post('tri','prix')"><?php echo $lang_fournisseur; ?></a></th>
					<th><a href="javascript:ecrit_ordre_et_post('tri','date')"><?php echo $lang_date; ?></a></th>
					<th><?php echo $lang_editer; ?></th>
					<th><?php echo $lang_effacer; ?></th>
				</tr>
				<?php
				$nombre = 1;
				while($data = mysql_fetch_array($req))
				{
					$num = $data['num'];
					$date = sql_to_date($data['date']);
					$lib = $data['lib'];
					$fou = $data['fournisseur'];
					$fou = stripslashes($fou);
					$montant = $data['prix'];
					$nombre = $nombre +1;
					if($nombre & 1)
					{
						$line="0";
					} else {
						$line="1";
					}
				?>
				<tr class="texte<?php echo "$line" ?>" onmouseover="this.className='highlight'" onmouseout="this.className='texte<?php echo "$line" ?>'">
					<td class="highlight" width="5%"><?php echo $num; ?></td>
					<td class="highlight" width="25%"><?php echo $lib; ?></td>
					<td class="highlight" width="15%" style="text-align:right"><?php echo montant_financier ($montant); ?> &nbsp;</td>
					<td class="highlight" width="20%"><?php echo $fou; ?></td>
					<td class="highlight" width="15%"><?php echo $date; ?></td>
					<td class="highlight" width="10%">
						<a href='dep_edit.php?num_dep=<?php echo $num; ?>' ><img border="0" alt="editer" src="image/edit.png" /></a>
					</td>
					<td class="highlight" width="10%">
						<a href="dep_delete.php?num=<?php echo $num; ?>" onclick="return confirmDelete('<?php echo "$lang_eff_conf_dep $num ?"; ?>')"> 
						<img border="0" src="image/delete.png" alt="effacer" /></a>
					</td>
				</tr>
				<?php
				}
				?>

				<tr>
					<td colspan="7" class="submit"></td>
				</tr>
			</table>
			</center>
		</td>
	</tr>



	<tr>
		<td>

			<?php
				//$filtre=$filtre_sans_prix;
		//um, lib, fournisseur, prix, DATE_FORMAT(date,'%d/%m/%Y') AS date
		//				FROM ${tblpref}depense";
		
				//somme des mois
				$sql_tot_mois="SELECT sum(prix) as tot_mois, month(date) as mois, year(date) as annee FROM ($sql) as table_filtre 
						GROUP BY month(date), year(date) ORDER BY date";
				$result_tot_mois=mysql_query( $sql_tot_mois ) or die("Erreur tot mois <br/>$sql_tot_mois<br/>" . mysql_error());
				//on stock dans un tableau pour les calculs des %
				while ($tot_mois=mysql_fetch_array($result_tot_mois))
				{
					$tab_tot_mois[$tot_mois['mois'] . $tot_mois['annee']] = $tot_mois['tot_mois'];
				}
				//echo $sql_tot_mois . "<br/>";

				//somme des fournisseurs
				$sql_tot_four="SELECT sum(prix) as tot_four,fournisseur FROM ($sql) as table_filtre GROUP BY fournisseur";
				$result_tot_four=mysql_query( $sql_tot_four ) or die("Erreur tot four <br/>" . mysql_error());
				//on stock dans un tableau pour les calculs des %
				while ($tot_four = mysql_fetch_array($result_tot_four))
				{
					$tab_tot_four[$tot_four['fournisseur']] = $tot_four['tot_four'];
				}
				//print_r($tab_tot_four);
				//echo $sql_tot_four;

				$nb_col=mysql_num_rows($result_tot_mois) * 2 + 4; // utilisé pour les colspan
				//$nb_ligne=mysql_num_rows($result_tot_four) + 2;

				$sql_total_tout="SELECT sum(prix) as total FROM ($sql) as table_filtre ";
				$result_tout=mysql_query($sql_total_tout) or die("Erreur calcul total <br/>$sql_total_tout<br/>" . mysql_error());
				$total=mysql_result($result_tout,0); //contient le total générale

				$calendrier = calendrier_local_mois ();
			?>

		</td>
	</tr>


</table>

<?php // on le sort du tableau principale afin qu'il puisse prendre toute la largeur de la page ?>

<center>
<table border="0" style="border:solid 3px">
	<caption>
		<?php aff_lang("Tableau de bord des dépenses") ?>
	</caption>

	<?php // 2eme ligne affichage des mois ?>
	<tr>
		<td class="texte1" colspan="2" style="border-bottom:solid 2px">&nbsp;</td>
		<?php
		mysql_data_seek($result_tot_mois,0);
		while ($tot_mois=mysql_fetch_array($result_tot_mois))
		{
			?>
			<td class="texte1" style="text-align:center;border-right:dotted 1px;border-bottom:solid 2px;border-left:solid 1px">
				<?php echo utf8_decode(ucfirst($calendrier[$tot_mois['mois']])) . " " . substr($tot_mois['annee'],2,2) ?>
			</td>
			<?php $pourcent=$tab_tot_mois[$tot_mois['mois'] . $tot_mois['annee']] / $total * 100; ?>
			<td class="texte1" style="text-align:right;border-right:solid 1px;border-bottom:solid 2px;background-image:url(include/stat_image.php?hauteur=<?php echo $pourcent / 4 ?>);background-repeat:repeat-x;background-position:bottom;">
				<?php echo montant_taux($pourcent,0); ?>
			</td>
			<?php
			//$total=$total + $tot_mois['mois'];

		}
		?>
		<td>&nbsp;</td>
		<td class="texte1" style=";border:solid 1px"><?php aff_lang("Total") ?></td>
	</tr>

	<?php // boucle sur chaque fournisseur 
	$sql_four = "SELECT DISTINCT fournisseur FROM ($sql) as table_filtre ";
	$sql_four.=" ORDER BY fournisseur";
	$result_four=mysql_query( $sql_four ) or die("Erreur select four <br/>$sql_four<br/>" . mysql_error());

	while ($four=mysql_fetch_array($result_four))
	{
	?>
	<tr>
		<td class="texte1" style="border-right:dotted 1px;border-bottom:solid 1px;border-left:solid 1px">
			<?php $fournisseur=$four['fournisseur'] ; echo $fournisseur; ?>
		</td>
			<?php $pourcent=$tab_tot_four[$fournisseur] / $total * 100; ?>
		<td class="texte1"  style="border-right:solid 1px;border-bottom:solid 1px;;background-image:url(include/stat_image.php?largeur=<?php echo $pourcent / 3 ?>);background-repeat:repeat-y;background-position:left;">
			<?php echo montant_taux($pourcent,0); ?>
		</td>
		<?php
		//boucle sur chaque mois
		mysql_data_seek($result_tot_mois,0);

		while ($tab_mois=mysql_fetch_array($result_tot_mois))
		{
			$mois=$tab_mois['mois'];
			$annee=$tab_mois['annee'];
			//somme pour ce mois pour ce fournisseur
			$sql_tot_four_par_mois="SELECT sum(prix) as tot_four_mois FROM ($sql) as table_filtre WHERE 
				fournisseur='$fournisseur' AND month(date)=$mois AND year(date)=$annee";

			//echo $sql_tot_four_par_mois . "<br/>";
			$result_tot_four_par_mois=mysql_query( $sql_tot_four_par_mois ) or die("Erreur calcul tot four par mois <br/>$sql_tot_four_par_mois<br/>" . mysql_error());
			
			$montant=mysql_result($result_tot_four_par_mois,0);

			?>
			<td class="texte0" style="text-align:right;border-right:dotted 1px;border-bottom:solid 1px;border-left:solid 1px">
				<?php echo montant_financier($montant); ?>
			</td>
			<td class="texte0" style="text-align:right;border-right:solid 1px;border-bottom:solid 1px;">
				<?php echo montant_taux($montant / $tab_tot_mois[$mois . $annee] * 100,0); ?>
			</td>

			<?php
		}
		?>

			<td>
				&nbsp;
			</td>

			<td class="totaltexte" style="text-align:right;border:solid 1px">
				<?php echo montant_financier($tab_tot_four[$fournisseur]); ?>
			</td>

	</tr>
	<?php
	}

	?>

	<tr>
		<td colspan="<?php echo $nb_col ?>">
			
		</td>
	</tr>
	<?php // Affichage des totaux par mois ?>
	<tr>
		<td colspan="2" class="texte0" style="text-align:center;border:solid 1px;">Total</td>

		<?php
			foreach ($tab_tot_mois as $tot_mois)
			{
			?>
			<td class="totaltexte" style="text-align:center;border:solid 1px" colspan="2"><?php echo montant_financier($tot_mois) ?></td>
			<?php
			}
		?>
		<td>&nbsp;</td>
		<td class="totaltexte" style="text-align:right;border:solid 1px"><?php echo montant_financier($total) ?></td>
	</tr>




</table>
</center>

<br/>

<hr/>

<br/>

</body>
</html>

